{
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.8.8 64-bit",
   "metadata": {
    "interpreter": {
     "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
    }
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\nInt64Index: 139 entries, 0 to 138\nData columns (total 36 columns):\n #   Column       Non-Null Count  Dtype         \n---  ------       --------------  -----         \n 0   月计划编号_x      139 non-null    object        \n 1   申请单位_x       139 non-null    object        \n 2   工作地点         139 non-null    object        \n 3   设备类型         139 non-null    object        \n 4   设备名称         139 non-null    object        \n 5   主要工作内容_x     139 non-null    object        \n 6   停役日期         139 non-null    datetime64[ns]\n 7   复役日期         139 non-null    datetime64[ns]\n 8   停电天数         139 non-null    int64         \n 9   电压           139 non-null    object        \n 10  施工单位         139 non-null    object        \n 11  检修性质         139 non-null    object        \n 12  项目负责人        139 non-null    object        \n 13  备注及说明        139 non-null    object        \n 14  计划来源_x       139 non-null    object        \n 15  计划执行情况_x     118 non-null    object        \n 16  补充备注_x       64 non-null     object        \n 17  周计划编号        84 non-null     object        \n 18  Unnamed: 18  0 non-null      float64       \n 19  项目           139 non-null    object        \n 20  项目细分         0 non-null      float64       \n 21  序号           84 non-null     float64       \n 22  计划停役时间       84 non-null     object        \n 23  计划复役时间       84 non-null     object        \n 24  厂站名称         84 non-null     object        \n 25  主要工作内容_y     84 non-null     object        \n 26  申请单位_y       84 non-null     object        \n 27  备注           72 non-null     object        \n 28  申请单编号        82 non-null     float64       \n 29  许可及调度单位      84 non-null     object        \n 30  计划来源_y       84 non-null     object        \n 31  计划执行情况_y     81 non-null     object        \n 32  月计划编号_y      0 non-null      float64       \n 33  工作执行单        74 non-null     object        \n 34  补充备注_y       6 non-null      object        \n 35  项目名称         2 non-null      float64       \ndtypes: datetime64[ns](2), float64(6), int64(1), object(27)\nmemory usage: 40.2+ KB\nNone\n<class 'pandas.core.frame.DataFrame'>\nInt64Index: 343 entries, 0 to 342\nData columns (total 36 columns):\n #   Column       Non-Null Count  Dtype         \n---  ------       --------------  -----         \n 0   序号           343 non-null    int64         \n 1   周计划编号        343 non-null    object        \n 2   计划停役时间       343 non-null    object        \n 3   计划复役时间       343 non-null    object        \n 4   厂站名称         343 non-null    object        \n 5   主要工作内容_x     343 non-null    object        \n 6   申请单位_x       343 non-null    object        \n 7   备注           189 non-null    object        \n 8   申请单编号        311 non-null    float64       \n 9   许可及调度单位      343 non-null    object        \n 10  计划来源_x       343 non-null    object        \n 11  计划执行情况_x     268 non-null    object        \n 12  月计划编号_x      0 non-null      float64       \n 13  工作执行单        186 non-null    object        \n 14  补充备注_x       18 non-null     object        \n 15  项目名称         2 non-null      float64       \n 16  月计划编号_y      84 non-null     object        \n 17  申请单位_y       84 non-null     object        \n 18  工作地点         84 non-null     object        \n 19  设备类型         84 non-null     object        \n 20  设备名称         84 non-null     object        \n 21  主要工作内容_y     84 non-null     object        \n 22  停役日期         84 non-null     datetime64[ns]\n 23  复役日期         84 non-null     datetime64[ns]\n 24  停电天数         84 non-null     float64       \n 25  电压           84 non-null     object        \n 26  施工单位         84 non-null     object        \n 27  检修性质         84 non-null     object        \n 28  项目负责人        84 non-null     object        \n 29  备注及说明        84 non-null     object        \n 30  计划来源_y       84 non-null     object        \n 31  计划执行情况_y     79 non-null     object        \n 32  补充备注_y       13 non-null     object        \n 33  Unnamed: 18  0 non-null      float64       \n 34  项目           84 non-null     object        \n 35  项目细分         0 non-null      float64       \ndtypes: datetime64[ns](2), float64(6), int64(1), object(27)\nmemory usage: 99.1+ KB\nNone\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "fpath = r'/Users/tangjx/Documents/python/file_tools/生产计划管理/周计划与工作执行情况核查表.xlsx'\n",
    "df_w = pd.read_excel(fpath, sheet_name='周计划', keep_default_na=True)\n",
    "df_m = pd.read_excel(fpath, sheet_name='月计划', keep_default_na=True)\n",
    "df1 = pd.merge(df_m, df_w, on = '周计划编号', how = 'left')\n",
    "df2 = pd.merge(df_w, df_m, on = '周计划编号', how='left')\n",
    "\n",
    "print(df1.info())\n",
    "print(df2.info())\n",
    "\n",
    "# df1.to_excel('月计划执行情况.xlsx')\n",
    "# df2.to_excel('周计划执行情况.xlsx')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      设备名称\n",
       "工作地点      \n",
       "云山变      3\n",
       "候青变      3\n",
       "冬青变      2\n",
       "同山变      1\n",
       "天一变      5\n",
       "威远变      5\n",
       "宁海变      2\n",
       "崇寿变      2\n",
       "广济变     11\n",
       "慈溪变      1\n",
       "担山变      2\n",
       "新模变      1\n",
       "新都变      2\n",
       "昆亭变      2\n",
       "桑田变      2\n",
       "江中变      2\n",
       "洋溪变      1\n",
       "溪凤变      1\n",
       "灰库变      2\n",
       "灵峰变      2\n",
       "牛牵变      1\n",
       "田野变      1\n",
       "白岳变      1\n",
       "白鹭变      4\n",
       "线路      50\n",
       "育才变      1\n",
       "越瓷变      1\n",
       "通济变      2\n",
       "邬隘变      6\n",
       "钱湖变      5\n",
       "陆埠变      1\n",
       "雁苍变     11\n",
       "集星变      2\n",
       "黄墩变      1"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>设备名称</th>\n    </tr>\n    <tr>\n      <th>工作地点</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>云山变</th>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>候青变</th>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>冬青变</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>同山变</th>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>天一变</th>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>威远变</th>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>宁海变</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>崇寿变</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>广济变</th>\n      <td>11</td>\n    </tr>\n    <tr>\n      <th>慈溪变</th>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>担山变</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>新模变</th>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>新都变</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>昆亭变</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>桑田变</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>江中变</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>洋溪变</th>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>溪凤变</th>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>灰库变</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>灵峰变</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>牛牵变</th>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>田野变</th>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>白岳变</th>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>白鹭变</th>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>线路</th>\n      <td>50</td>\n    </tr>\n    <tr>\n      <th>育才变</th>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>越瓷变</th>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>通济变</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>邬隘变</th>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>钱湖变</th>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>陆埠变</th>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>雁苍变</th>\n      <td>11</td>\n    </tr>\n    <tr>\n      <th>集星变</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>黄墩变</th>\n      <td>1</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "source": [
    "pd.pivot_table(df_m,index=['工作地点'],values=['设备名称'],aggfunc='count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "{'云山变': [136, 137, 138], '候青变': [23, 24, 25], '冬青变': [10, 11], '同山变': [56], '天一变': [48, 49, 50, 51, 52], '威远变': [57, 58, 59, 60, 61], '宁海变': [38, 39], '崇寿变': [5, 6], '广济变': [12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22], '慈溪变': [7], '担山变': [8, 9], '新模变': [121], '新都变': [119, 120], '昆亭变': [33, 34], '桑田变': [46, 47], '江中变': [31, 32], '洋溪变': [133], '溪凤变': [68], '灰库变': [27, 28], '灵峰变': [35, 36], '牛牵变': [40], '田野变': [53], '白岳变': [4], '白鹭变': [0, 1, 2, 3], '线路': [69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118], '育才变': [134], '越瓷变': [135], '通济变': [54, 55], '邬隘变': [62, 63, 64, 65, 66, 67], '钱湖变': [41, 42, 43, 44, 45], '陆埠变': [37], '雁苍变': [122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132], '集星变': [29, 30], '黄墩变': [26]}"
      ]
     },
     "metadata": {},
     "execution_count": 28
    }
   ],
   "source": [
    "df_m.groupby(['工作地点']).groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7fae51fe2400>"
      ]
     },
     "metadata": {},
     "execution_count": 25
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}